﻿using System;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Drawing;
using System.Web.UI.WebControls;
using Telerik.Web.UI;
using Aspose.Excel;

namespace LenderClearingHouse
{
    public partial class EscrowList : PageBase
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            Session["SourcePage"] = SourcePage.EscrowList;
            listFilter.SearchButton.Click += new ImageClickEventHandler(SearchButton_Click);

            if (!IsPostBack)
            {
                DataAccess da = new DataAccess();
                DataTable tbl = da.ExecuteSQL("select * from Config");

                foreach (DataRow dr in tbl.Rows)
                {
                    if (dr["Key"].ToString() == "Escrow10daysVariable")
                    {
                        tb10daysVariable.Text = dr["Value"].ToString().Replace(",", ".");
                    }
                }

                SaveSortedPropertyIdsToSession(null);
            }

            try
            {
                grid.PageSize = int.Parse(Session[GridPageSizeSessionKey].ToString());
            }
            catch
            {
                grid.PageSize = 20;
            }

            if (Session[PropertyDetails.STR_WE_ARE_ON_DETAILS] != null)
            {
                Session[PropertyDetails.STR_WE_ARE_ON_DETAILS] = null;
                object sortInfo = Session[GetLastSortingKey()];
                object pageInfo = Session[GetLastPagingKey()];

                if (sortInfo != null)
                {
                    string sortField = (sortInfo as string).Split(';')[0];
                    string sortOrder = (sortInfo as string).Split(';')[1];

                    grid.MasterTableView.SortExpressions.AddSortExpression(
                    new GridSortExpression()
                    {
                        FieldName = sortField,
                        SortOrder = sortOrder == "Ascending" ? GridSortOrder.Ascending : GridSortOrder.Descending
                    });
                }

                if (pageInfo != null)
                {
                    grid.CurrentPageIndex = (int)pageInfo;
                }
            }
        }

        void SaveSortedPropertyIdsToSession(string sortExpression)
        {
            if (ForceEdit)
                return;

            DataTable tbl = (SqlDataSource.Select(DataSourceSelectArguments.Empty) as DataView).Table;
            List<int> ids = new List<int>();

            if (string.IsNullOrEmpty(sortExpression))
            {
                foreach (DataRow dr in tbl.Rows)
                {
                    ids.Add(Convert.ToInt32(dr["PropertyId"]));
                }
            }
            else
            {
                foreach (DataRow dr in tbl.Select("", sortExpression))
                {
                    ids.Add(Convert.ToInt32(dr["PropertyId"]));
                }
            }

            Session["AllPropertiesIds"] = ids.ToArray();
        }

        void SearchButton_Click(object sender, ImageClickEventArgs e)
        {
            grid.Rebind();
        }

        protected void SqlDataSource_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
        {
            if (!string.IsNullOrEmpty(Apn))
                e.Command.Parameters["@Apn"].Value = Apn;
            else
                e.Command.Parameters["@Apn"].Value = listFilter.Apn;
            e.Command.Parameters["@PropLocation"].Value = listFilter.Address;
            e.Command.Parameters["@Zip"].Value = listFilter.Zip;
            e.Command.Parameters["@archive"].Value = archive;
        }

        protected string GetLastSortingKey()
        {
            return string.Format("last_sorting_escrowlist");
        }

        protected string GetLastPagingKey()
        {
            return string.Format("last_paging_escrowlist");
        }

        protected void grid_SortCommand(object sender, GridSortCommandEventArgs e)
        {
            string lastSortingKey = GetLastSortingKey();
            Session[lastSortingKey] = e.SortExpression + ";" + e.NewSortOrder.ToString();

            SaveSortedPropertyIdsToSession(e.SortExpression + " "
                + (e.NewSortOrder.ToString().StartsWith("Asc") ? "ASC" : "DESC"));
        }

        protected void grid_PageIndexChanged(object sender, GridPageChangedEventArgs e)
        {
            Session[GetLastPagingKey()] = e.NewPageIndex;
        }

        protected void grid_UpdateCommand(object sender, Telerik.Web.UI.GridCommandEventArgs e)
        {
            GridEditableItem editedItem = e.Item as GridEditableItem;
            EscrowEditControl userControl = (EscrowEditControl)e.Item.FindControl(GridEditFormItem.EditFormUserControlID);

            DataAccess da = new DataAccess();
            da.ExecuteProcedure("updateLvreo_MasterListEscrow",
                new string[]
                {
                    "@PropertyId"
	                ,"@PurchaseAgreementReceived"
	                ,"@CAP"
	                ,"@TitleCompanyFront"
	                ,"@Loan"
	                ,"@LoanInfo"
	                ,"@EffectiveDateBank"
	                ,"@ECOE_F"
	                ,"@AdjPurchasePrice"
	                ,"@RECORDED_F"
	                ,"@CommentsFront"
	                ,"@ListingAgreement"
	                ,"@ContractDate"
	                ,"@ECOE_B"
	                ,"@Recorded_B"
	                ,"@PurchaseType"
	                ,"@Incentive"
	                ,"@HOAManagement"
	                ,"@MonthlyHOADues"
	                ,"@PastDueHOA"
	                ,"@ResalePacketOrdered"
	                ,"@ResalePkgToAgent"
	                ,"@TitleCompanyBack"
	                ,"@BuyerName"
	                ,"@COOPAgent"
	                ,"@Lender"
	                ,"@WalkThrough"
	                ,"@DocsIn"
	                ,"@SellerSign"
	                ,"@BuyerSign"
	                ,"@CommentsBack"
                },
                new object[]
                {
                    (long)editedItem.OwnerTableView.DataKeyValues[editedItem.ItemIndex]["PropertyId"]
	                ,userControl.edPurchaseAgreementReceived.DbSelectedDate
	                ,userControl.edCAP.Text
	                ,userControl.edTitleCompanyFront.Text
	                ,userControl.edLoan.Text
	                ,userControl.edLoanInfo.Text
	                ,userControl.edEffectiveDateBank.DbSelectedDate
	                ,userControl.edECOE_F.DbSelectedDate
	                ,userControl.edAdjPurchasePrice.DbValue
	                ,userControl.edRECORDED_F.DbSelectedDate
	                ,userControl.edCommentsFront.Value
	                ,userControl.edListingAgreement.DbSelectedDate
	                ,userControl.edContractDate.DbSelectedDate
	                ,userControl.edECOE_B.DbSelectedDate
	                ,userControl.edRecorded_B.DbSelectedDate
	                ,userControl.edPurchaseType.Text
	                ,userControl.edIncentive.Text
	                ,userControl.edHOAManagement.Text
	                ,userControl.edMonthlyHOADues.DbValue
	                ,userControl.edPastDueHOA.Text
	                ,userControl.edResalePacketOrdered.DbSelectedDate
	                ,userControl.edResalePkgToAgent.Text
	                ,userControl.edTitleCompanyBack.Text
	                ,userControl.edBuyerName.Text
	                ,userControl.edCOOPAgent.Text
	                ,userControl.edLender.Text
	                ,userControl.edWalkThrough.DbSelectedDate
	                ,userControl.edDocsIn.DbSelectedDate
	                ,userControl.edSellerSign.DbSelectedDate
	                ,userControl.edBuyerSign.DbSelectedDate
	                ,userControl.edCommentsBack.Value
                });
        }

        protected void grid_ItemDataBound(object sender, GridItemEventArgs e)
        {
            if (e.Item is GridPagerItem)
            {
                GridPagerItem item = e.Item as GridPagerItem;
                RadComboBox combo = (RadComboBox)item.FindControl("PageSizeComboBox");

                RadComboBoxItem newItem = combo.Items.FindItemByValue("100");
                if (newItem == null)
                {
                    RadComboBoxItem citem = new RadComboBoxItem("100", "100");
                    citem.Attributes.Add("ownerTableViewId", e.Item.OwnerTableView.ClientID);
                    combo.Items.Add(citem);
                }
                else
                {
                    newItem.Text = "100";
                }

                newItem = combo.Items.FindItemByValue("1000000");
                if (newItem == null)
                {
                    RadComboBoxItem citem = new RadComboBoxItem("ALL", "1000000");
                    citem.Attributes.Add("ownerTableViewId", e.Item.OwnerTableView.ClientID);
                    combo.Items.Add(citem);
                }
                else
                {
                    newItem.Text = "ALL";
                }
            }
            else if (e.Item is GridDataItem)
            {
                GridDataItem itm = e.Item as GridDataItem;
                DataRowView drw = itm.DataItem as DataRowView;
                if (drw["Color"] != DBNull.Value)
                {
                    System.Drawing.Color c = System.Drawing.Color.Purple;

                    if (drw["Color"].ToString() == "Yellow")
                    {
                        c = System.Drawing.Color.Yellow;
                    }
                    else
                        if (drw["Color"].ToString() == "PastelPurple")
                        {
                            c = System.Drawing.Color.FromArgb(189, 132, 173);
                        }
                        else
                            if (drw["Color"].ToString() == "Orange")
                            {
                                c = System.Drawing.Color.Orange;
                            }

                    itm.BackColor = c;
                }
            }
        }

        protected void Grid_ItemEvent(object sender, GridItemEventArgs e)
        {
            if (e.Item is GridEditFormItem)
            {
                if (!((e.Item as GridEditFormItem).DataItem is DataRowView)) return;

                DataRowView drw = (DataRowView)(e.Item as GridEditFormItem).DataItem;
                if (drw == null) return;

                if (drw["NetProfit"] != DBNull.Value)
                {
                    Session["NetProfit"] = Convert.ToInt32(drw["NetProfit"]);
                }
                else Session["NetProfit"] = 0;

                if (drw["BuyerPurchasePrice"] != DBNull.Value)
                {
                    Session["BuyerPurchasePrice"] = Convert.ToInt32(drw["BuyerPurchasePrice"]);
                }
                else Session["BuyerPurchasePrice"] = 0;

                Session["PropertyLocation"] = Convert.ToString(drw["PropertyLocation"]);
                Session["InvestorName"] = Convert.ToString(drw["InvestorName"]);
                Session["Bedrooms"] = Convert.ToString(drw["Bedrooms"]);
                Session["Bathrooms"] = Convert.ToString(drw["Bathrooms"]);
                Session["ConstructYear"] = Convert.ToString(drw["ConstructYear"]);
                Session["GarageSqft"] = Convert.ToString(drw["GarageSqft"]);
                Session["Pool"] = Convert.ToString(drw["Pool"]);
                Session["TotalSqft"] = Convert.ToString(drw["TotalSqft"]);
            }
        }

        protected void lnkRefreshGrid_Click(object sender, EventArgs e)
        {
            grid.Rebind();
        }

        protected void lnkExportToExcel_Click(object sender, EventArgs e)
        {
            DataAccess da = new DataAccess();
            DataTable dt = da.ExecuteSQL(string.Format("getLvreo_MasterListEscrow '{0}', '{1}', '{2}', 1, 0",
              listFilter.Apn,
              listFilter.Address,
              listFilter.Zip));
            ExportToExcel(dt);
        }

        void ExportToExcel(DataTable dt)
        {
            /*DataAccess da = new DataAccess();
            DataTable dt = da.ExecuteSQL(string.Format("getLvreo_MasterListEscrow '{0}', '{1}', '{2}'",
              listFilter.Apn,
              listFilter.Address,
              listFilter.Zip));*/

            int startRow = 1;
            string[] listFields = new string[] { "PurchaseAgreementReceived", 
                    "CAP", "DateAccept", "Apn", "PropertyLocation", "ZipCode", "InvestorName", "TitleCompanyFront", "Loan", "LoanInfo", 
                    "EffectiveDateBank", "ECOE_F", "PurchasePrice", "AdjPurchasePrice", "RECORDED_F", "CommentsFront", 
                    "", "ListingAgreement", "ContractDate", "ECOE_B", "Recorded_B", "BuyerPurchasePrice", "PurchaseType", "Incentive", 
                    "HOAManagement", "MonthlyHOADues", "PastDueHOA", "ResalePacketOrdered", "ResalePkgToAgent", "TitleCompanyBack", 
                    "BuyerName", "COOPAgent", "Lender", "WalkThrough", "DocsIn", "SellerSign", "BuyerSign", "CommentsBack" ,"NetProfit"};
            Excel ex = Utilities.GridToExcel(dt, listFields, "Escrow", false, ref startRow, 8);

            Worksheet ws = ex.Worksheets[0];
            for (int i = 0; i < dt.Rows.Count; ++i)
                for (int j = 0; j < listFields.Length; ++j)
                {
                    ws.Cells[i + 1, j].Style.Borders[BorderType.LeftBorder].Color = Color.Black;
                    ws.Cells[i + 1, j].Style.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;

                    ws.Cells[i + 1, j].Style.Borders[BorderType.RightBorder].Color = Color.Black;
                    ws.Cells[i + 1, j].Style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;

                    ws.Cells[i + 1, j].Style.Borders[BorderType.TopBorder].Color = Color.Black;
                    ws.Cells[i + 1, j].Style.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;

                    ws.Cells[i + 1, j].Style.Borders[BorderType.BottomBorder].Color = Color.Black;
                    ws.Cells[i + 1, j].Style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
                }

            ex.Save("Escrow.xls", FileFormatType.Excel2003, SaveType.OpenInExcel, HttpContext.Current.Response);
            HttpContext.Current.Response.End();
        }

        protected void lnkBackendReport_Click(object sender, EventArgs e)
        {
            DataAccess da = new DataAccess();
            DataTable dt = da.ExecuteSQL(string.Format("getLvreo_MasterListEscrow '{0}', '{1}', '{2}', 0, 1",
              listFilter.Apn,
              listFilter.Address,
              listFilter.Zip));
            ExportToExcel(dt);
        }

        bool archive = false;

        protected void LinkButton1_Click(object sender, EventArgs e)
        {
            archive = true;
            grid.Rebind();
        }

        protected void btnSave_Click(object sender, EventArgs e)
        {
            DataAccess da = new DataAccess();
            da.ExecuteSQLNonQuery(
              string.Format("update Config set [Value] = '{0}' where [Key] = 'Escrow10daysVariable'",
              tb10daysVariable.Text));
            grid.Rebind();
        }

        protected void cbProperty_SelectedIndexChanged(object sender, RadComboBoxSelectedIndexChangedEventArgs e)
        {
            DataAccess da = new DataAccess();
            DataTable dt = da.GetDataTable("getLvreo_MasterListEscrow", new string[] { "@PropLocation" }, new object[] { cbProperty.Text });
            if (dt.Rows.Count > 0)
            {
                btnShowNetSheet.Enabled = true;
                btnShowNetSheet.OnClientClick = string.Format("OpenNetSheetDialogByPrice({0}, {1}, {2}); return false;",
                    dt.Rows[0]["BuyerPurchasePrice"] == DBNull.Value ? "0" : Convert.ToInt32(dt.Rows[0]["BuyerPurchasePrice"]).ToString()
                    , dt.Rows[0]["RehabCosts"] == DBNull.Value ? "0" : Convert.ToInt32(dt.Rows[0]["RehabCosts"]).ToString()
                    , Convert.ToInt32(dt.Rows[0]["PropertyId"]));

                if (dt.Rows[0]["NetProfit"] != DBNull.Value)
                {
                    Session["NetProfit"] = Convert.ToInt32(dt.Rows[0]["NetProfit"]);
                }
                else Session["NetProfit"] = 0;

                if (dt.Rows[0]["BuyerPurchasePrice"] != DBNull.Value)
                {
                    Session["BuyerPurchasePrice"] = Convert.ToInt32(dt.Rows[0]["BuyerPurchasePrice"]);
                }
                else Session["BuyerPurchasePrice"] = 0;

                if (dt.Rows[0]["RehabCosts"] != DBNull.Value)
                {
                    Session["RehabCosts"] = Convert.ToInt32(dt.Rows[0]["RehabCosts"]);
                }
                else Session["RehabCosts"] = 0;

                Session["PropertyLocation"] = Convert.ToString(dt.Rows[0]["PropertyLocation"]);
                Session["InvestorName"] = Convert.ToString(dt.Rows[0]["InvestorName"]);
                Session["Bedrooms"] = Convert.ToString(dt.Rows[0]["Bedrooms"]);
                Session["Bathrooms"] = Convert.ToString(dt.Rows[0]["Bathrooms"]);
                Session["ConstructYear"] = Convert.ToString(dt.Rows[0]["ConstructYear"]);
                Session["GarageSqft"] = Convert.ToString(dt.Rows[0]["GarageSqft"]);
                Session["Pool"] = Convert.ToString(dt.Rows[0]["Pool"]);
                Session["TotalSqft"] = Convert.ToString(dt.Rows[0]["TotalSqft"]);
            }
        }

        string GridPageSizeSessionKey { get { return "GridPageSize_" + this.GetType().Name; } }

        protected void grid_PageSizeChanged(object sender, GridPageSizeChangedEventArgs e)
        {
            Session[GridPageSizeSessionKey] = e.NewPageSize.ToString();
        }

        protected void grid_ItemCreated(object sender, GridItemEventArgs e)
        {
            if (!Page.IsPostBack && e.Item is GridEditableItem && ForceEdit)
            {
                e.Item.Edit = true;
            }
        }

        protected string Apn
        {
            get
            {
                string result = "";
                if (Request.QueryString["apn"] != null)
                    result = Request.QueryString["apn"].ToString();

                return result;
            }
        }

        protected bool ForceEdit
        {
            get
            {
                return (Request.QueryString["edit"] != null);
            }
        }

    }
}